package com.fare.taluo.core.domain.crawl.impl;
/**
 * JDBC batchUpdate 批量处理（效率比HIBERNATE高）
*/
import java.io.Serializable;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.dayatang.hibernate.EntityRepositoryHibernate;
import com.fare.taluo.core.domain.crawl.FdcCrawlStatisticsEntity;
import com.fare.taluo.core.domain.crawl.FdcCrawlStatisticsEntityRepository;
import com.fare.taluo.core.domain.crawl.FdcCrawlStatisticsForFlightLineEntity;
import com.fare.taluo.core.domain.crawl.FdcCrawlStatisticsForGrabDateEntity;
import com.fare.taluo.util.SpringUtil;
@Repository("fdcCrawlStatisticsEntityRepository")
public class FdcCrawlStatisticsEntityRepositoryImpl extends EntityRepositoryHibernate implements FdcCrawlStatisticsEntityRepository {
	
	private JdbcTemplate jdbctemp=(JdbcTemplate) SpringUtil.getBean("jdbcTemplate");
	
	/**
	 * for finished tables
	 */	
	@Override
	public void saveDatas(final List<FdcCrawlStatisticsEntity> entityList) {
		final String sql = "insert fdc_crawl_statistics(create_Time,from_City,from_City_Name,to_City," +
				"to_City_Name,data_Sources,grab_Date,grab_channel_Id,grab_Channel,hbase_Key," +
				"totalNum,successNum,failNum,flightDaysNum,actualFlightDayNum,channel_source_data_info,channel_lines_info,flight_date_info) " +
				"values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		
		jdbctemp.batchUpdate(sql, new BatchPreparedStatementSetter() {
            public void setValues(java.sql.PreparedStatement ps, int index) throws SQLException {
            	FdcCrawlStatisticsEntity entity = entityList.get(index);
            	
//            	ps.setLong(1, entity.getId());
            	ps.setDate(1, new java.sql.Date(System.currentTimeMillis()));
            	ps.setString(2, entity.getFromCity());
            	ps.setString(3, entity.getFromCityName());
            	ps.setString(4, entity.getToCity());
            	ps.setString(5, entity.getToCityName());
            	ps.setLong(6, entity.getDataSources());
            	ps.setDate(7,entity.getGrabDate());
            	ps.setString(8,entity.getGrabChanelIdStr());
            	ps.setString(9, entity.getGrabChannelName());
            	ps.setString(10, entity.getHbaseKey());
            	ps.setLong(11, entity.getTotalNum());
            	ps.setLong(12, entity.getSuccessNum());
            	ps.setLong(13,entity.getFailNum());
            	ps.setInt(14, entity.getFlightDaysNum());
            	ps.setInt(15, entity.getActualFlightDayNum());
            	ps.setString(16, entity.getChannelSourceDataInfo());
            	ps.setString(17,entity.getChannelLinesInfo());
            	ps.setString(18, entity.getFlightDateInfo());
            }
            
            public int getBatchSize() {
                return entityList.size();
            }
        });
		
	}
	
	
	/**
	 * delete by citys and  grabDate for FdcCrawlStatisticsEntity
	 */	
	@Override
	public void deleteEntitys(String fromCity, String toCity, Date grabDate) {
		final String sql = "DELETE from fdc_crawl_statistics where from_city=? and to_city=? and grab_date=?";
		jdbctemp.update(sql, new Object[]{fromCity,toCity,grabDate});
		
	}
	
	/**
	 * for update tables
	 */	
	@Override
	public void updateDatas(final List<FdcCrawlStatisticsEntity> entityList) {
		final String sql = "update fdc_crawl_statistics set create_time=? ,data_Sources =? ,grab_channel_Id=? ," +
				"grab_Channel=? ,hbase_Key=? ,totalNum=? ,successNum=? ,failNum=? ,flightDaysNum=? ,actualFlightDayNum=? ," +
				"channel_source_data_info=? ,channel_lines_info=? ,flight_date_info=?  where from_city =? and to_city =? and grab_Date=? ";
		
		jdbctemp.batchUpdate(sql, new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps, int index) throws SQLException {
				FdcCrawlStatisticsEntity entity = entityList.get(index);
				ps.setDate(1, new java.sql.Date(System.currentTimeMillis()));
            	ps.setLong(2, entity.getDataSources());
            	ps.setString(3,entity.getGrabChanelIdStr());
            	ps.setString(4, entity.getGrabChannelName());
            	ps.setString(5, entity.getHbaseKey());
            	ps.setLong(6, entity.getTotalNum());
            	ps.setLong(7, entity.getSuccessNum());
            	ps.setLong(8,entity.getFailNum());
            	ps.setInt(9, entity.getFlightDaysNum());
            	ps.setInt(10, entity.getActualFlightDayNum());
            	ps.setString(11, entity.getChannelSourceDataInfo());
            	ps.setString(12,entity.getChannelLinesInfo());
            	ps.setString(13, entity.getFlightDateInfo());
            	ps.setString(14, entity.getFromCity());
            	ps.setString(15, entity.getToCity());
            	ps.setDate(16,entity.getGrabDate());
			}
			
			@Override
			public int getBatchSize() {
				return entityList.size();
			}
		});
		
	}
	
	/**
	 * for grabDate tables
	 */	
	@Override
	public void saveDatasForGrabDate(final List<FdcCrawlStatisticsForGrabDateEntity> entityLists) {
		final String sql = "insert FDC_CRAWL_STATISTICS_GRABDATE(grab_date,create_time,day_source_data,day_line_numbers," +
				"line_by_channel,source_by_channel,line_city_by_channel)" +
				"values(?,?,?,?,?,?,?)";
		jdbctemp.batchUpdate(sql, new BatchPreparedStatementSetter() {
            public void setValues(java.sql.PreparedStatement ps, int index) throws SQLException {
            	FdcCrawlStatisticsForGrabDateEntity entity = entityLists.get(index);
            	
            	ps.setDate(1, entity.getGrabDate());
            	ps.setDate(2,new java.sql.Date(System.currentTimeMillis()));
            	ps.setLong(3, entity.getDayTotalSourceData());
            	ps.setLong(4, entity.getDayTotalLineNumbers());
            	ps.setString(5, entity.getLineByChannel());
            	ps.setString(6, entity.getSourceByChannel());
            	ps.setString(7, entity.getLineCityByChannel());
            }
            
            public int getBatchSize() {
                return entityLists.size();
            }
        });
		
	}
	/**
	 * for grabDate tables 
	 * update
	 */	
	@Override
	public void updateDatasForGrabDate(final List<FdcCrawlStatisticsForGrabDateEntity> entityList) {
		final String sql = "update FDC_CRAWL_STATISTICS_GRABDATE set create_time=? ,day_source_data=? ," +
				"day_line_numbers=? ,line_by_channel=? ,source_by_channel=? ,line_city_by_channel =? where grab_date =?";
		jdbctemp.batchUpdate(sql, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int index) throws SQLException {
				FdcCrawlStatisticsForGrabDateEntity entity = entityList.get(index);
            	ps.setDate(1, new java.sql.Date(System.currentTimeMillis()));
            	ps.setLong(2, entity.getDayTotalSourceData());
            	ps.setLong(3, entity.getDayTotalLineNumbers());
            	ps.setString(4, entity.getLineByChannel());
            	ps.setString(5, entity.getSourceByChannel());
            	ps.setString(6, entity.getLineCityByChannel());
            	ps.setDate(7, entity.getGrabDate());
			}
			@Override
			public int getBatchSize() {
				return entityList.size();
			}
		});
	}
	
	
	/**
	 * delete by grabDate for FDC_CRAWL_STATISTICS_GRABDATE
	 */	
	@Override
	public void deleteEntityByGrabDate( Date grabDate) {
		final String sql = "DELETE from FDC_CRAWL_STATISTICS_GRABDATE where grab_date=?";
		jdbctemp.update(sql, new Object[]{grabDate});
		
	}
	
	/**
	 * for flightLine tables(insert)
	 */	
	@Override
	public void saveDatasForFlightLine(final List<FdcCrawlStatisticsForFlightLineEntity> entityList) {
		final String sql = "insert FDC_CRAWL_STATISTICS_FLIGHTLINE(create_time,from_city,from_city_name," +
				"to_city,to_city_name,grab_start_date,grab_end_date,grab_total_source)" +
				"values(?,?,?,?,?,?,?,?)";
		jdbctemp.batchUpdate(sql, new BatchPreparedStatementSetter() {
            public void setValues(java.sql.PreparedStatement ps, int index) throws SQLException {
            	FdcCrawlStatisticsForFlightLineEntity entity = entityList.get(index);
            	
            	//ps.setDate(1, entity.getCreateTime());//new java.sql.Date(System.currentTimeMillis())
            	ps.setDate(1, new java.sql.Date(System.currentTimeMillis()));
            	ps.setString(2, entity.getFromCity());
            	ps.setString(3, entity.getFromCityName());
            	ps.setString(4, entity.getToCity());
            	ps.setString(5, entity.getToCityName());
            	ps.setDate(6, entity.getGrabStartDate());
            	ps.setDate(7, entity.getGrabEndDate());
            	ps.setLong(8, entity.getGrabTotalSource());
            	
            }
            
            public int getBatchSize() {
                return entityList.size();
            }
        });
		
	}

	/**
	 * for flightLine tables(update)
	 */	
	@Override
	public void updateDatasForFlightLine(final List<FdcCrawlStatisticsForFlightLineEntity> updateListEntity) {
		final String sql = "update FDC_CRAWL_STATISTICS_FLIGHTLINE set create_time=? ,grab_end_date =? ,grab_total_source=?" +
				" where from_city =? and to_city =?";
		jdbctemp.batchUpdate(sql, new BatchPreparedStatementSetter() {
            public void setValues(java.sql.PreparedStatement ps, int index) throws SQLException {
            	FdcCrawlStatisticsForFlightLineEntity entity = updateListEntity.get(index);
            	
            	ps.setDate(1, new java.sql.Date(System.currentTimeMillis()));
            	ps.setDate(2,entity.getGrabEndDate());
            	ps.setLong(3, entity.getGrabTotalSource());
            	ps.setString(4, entity.getFromCity());
            	ps.setString(5, entity.getToCity());
            	
            }
            
            public int getBatchSize() {
                return updateListEntity.size();
            }
        });
		
	}

	
	/**
	 * //select by grabDate for FdcCrawlStatisticsEntity
	 */	
	@SuppressWarnings("unchecked")
	@Override
	public List<FdcCrawlStatisticsEntity> findByGrabDate(String fromCity,String toCity,Date date1, Date date2,Date dateGrabDate) {
	/*
	   //hibernate 查询
	    StringBuffer jpql =new StringBuffer("from FdcCrawlStatisticsEntity plane where 1=1 ");
		List<Object> conditionVals = new ArrayList<Object>();
		if(!fromCity.equals("") && fromCity !=null){
			jpql.append(" and plane.fromCity  = ? ");
			conditionVals.add(fromCity);
		}
		if(!fromCity.equals("") && fromCity !=null){
			jpql.append(" and plane.toCity  = ? ");
			conditionVals.add(toCity);
		}
		if(date1 !=null && !date1.equals("") ){
			jpql.append(" and plane.grabDate >= ? ");
			conditionVals.add(date1);	
		}
		if(date2 !=null && !date2.equals("")){
			jpql.append(" and plane.grabDate <= ? ");
			conditionVals.add(date2);
		}
		if(dateGrabDate !=null && !dateGrabDate.equals("")){
			jpql.append(" and plane.grabDate = ? ");
			conditionVals.add(dateGrabDate);
		}
		
		*/
		
		//jdbc 查询
		StringBuffer jpql =new StringBuffer("select * from fdc_crawl_statistics where 1=1 ");
		if(!fromCity.equals("") && fromCity !=null){
			jpql.append(" and from_City  = '"+fromCity+"' ");
		}
		if(!fromCity.equals("") && fromCity!=null){
			jpql.append(" and to_City  ='"+toCity+"' ");
		}
		if(date1 !=null && !date1.equals("") ){
			jpql.append(" and grab_date >=STR_TO_DATE('"+date1+"','%Y-%m-%d') ");
		}
		if(date2 !=null && !date2.equals("")){
			jpql.append(" and grab_date <=STR_TO_DATE('"+date2+"','%Y-%m-%d') ");
		}
		if(dateGrabDate !=null && !dateGrabDate.equals("")){
			jpql.append(" and grab_date =STR_TO_DATE('"+dateGrabDate+"','%Y-%m-%d') ");
		}
		
		List<FdcCrawlStatisticsEntity> list=new ArrayList<FdcCrawlStatisticsEntity>();
		list=(List<FdcCrawlStatisticsEntity>)jdbctemp.query(jpql.toString(), new ItemMapper());
//		list =super.find(jpql.toString(),conditionVals.toArray(),FdcCrawlStatisticsEntity.class);
		return list;
	}
	
	@SuppressWarnings({ "rawtypes", "serial" })
	protected class ItemMapper implements RowMapper ,Serializable {
		@Override
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
//			List<FdcCrawlStatisticsEntity> itemList =new ArrayList<FdcCrawlStatisticsEntity>();
			FdcCrawlStatisticsEntity item = new FdcCrawlStatisticsEntity();  
			item.setCreateTime(rs.getDate("create_time"));
			item.setFromCity(rs.getString("from_city"));
			item.setToCity(rs.getString("to_city"));
			item.setFromCityName(rs.getString("from_city_name"));
			item.setToCityName(rs.getString("to_city_name"));
			item.setDataSources(rs.getLong("data_sources"));
			item.setGrabDate(rs.getDate("grab_date"));
			item.setGrabChanelIdStr(rs.getString("grab_channel_id"));
			item.setChannelSourceDataInfo(rs.getString("channel_source_data_info"));
			item.setGrabChannelName(rs.getString("grab_channel"));
			item.setHbaseKey(rs.getString("hbase_key"));
			item.setTotalNum(rs.getInt("totalNum"));
			item.setSuccessNum(rs.getInt("successNum"));
			item.setFailNum(rs.getInt("failNum"));
			item.setFlightDaysNum(rs.getInt("flightDaysNum"));
			item.setActualFlightDayNum(rs.getInt("actualFlightDayNum"));
			item.setFlightDateInfo(rs.getString("flight_date_info"));
			item.setChannelLinesInfo(rs.getString("channel_lines_info"));
			item.setId(rs.getString("id"));
			
			return item;
		}  
		
	}

}


